package t1dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import t1bean.StudentBean;
import t1jdbc.JDBCUtil;

public class StudentActionDao {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private SetSql ss=new SetSql();

	public int insert(StudentBean student) throws SQLException {
		String sql = "insert into student(number,name,major_number) values(?,?,?)";
		try {
			conn = JDBCUtil.getConnection();
			conn.setAutoCommit(false);

			ps = conn.prepareStatement(sql);
			ps.setInt(1, student.getNumber());
			ps.setString(2, student.getName());
			ps.setInt(3, student.getMajorNumber());
			System.out.println(ps);
			int i = ps.executeUpdate();
			
			conn.commit();
			return i;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			JDBCUtil.close(conn, ps);
			conn = null;
			ps = null;
		}
		return 0;
	}

	public int update(StudentBean student, String name) throws SQLException {
		String sql = "update student set ";
		List<Object> list = new ArrayList<>();
		sql = ss.setSql(student, list, sql, 1);
		sql += " where name=?";
//		System.out.println(sql);
		list.add(name);
		try {
			conn = JDBCUtil.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			int i = ps.executeUpdate();
			conn.commit();
			return i;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			conn.rollback();
		} finally {
			JDBCUtil.close(conn, ps);
			conn = null;
			ps = null;
		}
		return 0;
	}

	public int delete(StudentBean student) throws SQLException {
		String sql = "delete from student where name=? and number=?";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			conn.setAutoCommit(false);
			ps.setString(1, student.getName());
			ps.setInt(2, student.getNumber());
			int i = ps.executeUpdate();
			conn.commit();
			return i;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			conn.rollback();
		} finally {
			JDBCUtil.close(conn, ps);
			conn = null;
			ps = null;
		}
		return 0;
	}

	public List<StudentBean> selectAll() throws SQLException {
		String sql = "select * from student ";
		List<StudentBean> list = new ArrayList<>();
		try {
			conn = JDBCUtil.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			conn.commit();
			while (rs.next()) {
				StudentBean stu = new StudentBean();
				stu.setId(rs.getInt("id"));
				stu.setMajorNumber(rs.getInt("major_number"));
				stu.setName(rs.getString("name"));
				stu.setNumber(rs.getInt("number"));
				list.add(stu);
			}
			return list;
		} catch (ClassNotFoundException | SQLException e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
			conn = null;
			ps = null;
			rs = null;
		}
		return null;
	}

	public List<StudentBean> select(StudentBean student) throws SQLException {
		String sql = "select * from student where ";
		List<Object> oList = new ArrayList<>();
		sql = ss.setSql(student, oList, sql, 2);
//		System.out.println(sql);
		List<StudentBean> list = new ArrayList<>();
		try {

			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			conn.setAutoCommit(false);
			for (int i = 0; i < oList.size(); i++) {
				ps.setObject(i + 1, oList.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				StudentBean stu = new StudentBean();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setMajorNumber(rs.getInt("major_number"));
				stu.setNumber(rs.getInt("number"));
				list.add(stu);
			}

			return list;

		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			conn.rollback();
		} finally {
			JDBCUtil.close(conn, ps, rs);
			conn = null;
			ps = null;
			rs = null;
		}
		return null;
	}

	

}
